const { Sequelize, DataTypes } = require('sequelize');

const pgConfig = {
    host: "192.168.1.3",
    port: 5432,
    database: "dbmt2309",
    username: "postgres",
    password: "123"
};
const sequelize = new Sequelize(pgConfig.database, pgConfig.username, pgConfig.password, {
    dialect: 'postgres',
    host: pgConfig.host
});

const Product = sequelize.define('test_product', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    details: {
        type: DataTypes.JSONB, // 定义 JSONB 类型字段
        allowNull: true
    }
});
(async ()=>{
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');


    sequelize.sync().then(() => {
        console.log('Table created');
    }).catch(err => console.error('Unable to create table:', err));

    // 添加 5 条测试数据
/*    try {
        await Product.bulkCreate([
            { name: 'Product 1', details: { price: 100, description: 'Description for Product 1' } },
            { name: 'Product 2', details: { price: 200, description: 'Description for Product 2' } },
            { name: 'Product 3', details: { price: 300, description: 'Description for Product 3' } },
            { name: 'Product 4', details: { price: 400, description: 'Description for Product 4' } },
            { name: 'Product 5', details: { price: 500, description: 'Description for Product 5' } }
        ]);
        console.log('5 test data records inserted');
    } catch (error) {
        console.error('Error inserting test data:', error);
    }*/

    /*
      Product.update({ details: { price: 200 } }, {
        where: { id: 1 }
    }).then(() => {
        console.log('Product details updated');
    }).catch(err => console.error('Error updating product details:', err));
    */

    // 假设这里是要更新的新值
/*
    const updatedValues = { price: 9999, key2: 'value2' };

    // 执行原始 SQL 查询
    const queryString = `UPDATE test_products SET details = details || '${JSON.stringify(updatedValues)}' WHERE id = ${1} RETURNING *`;
    const updatedResult = await sequelize.query(queryString, { type: sequelize.QueryTypes.UPDATE });
    console.log('Updated result:', JSON.stringify(updatedResult));
*/


    Product.findAll({
        where: {
            details: {
                price: { [Sequelize.Op.gt]: 9998 } // 查询价格大于 100 的产品
            }
        }
    }).then(products => {
        console.log(products);
    }).catch(err => console.error('Error querying products:', err));

})()
